#!/usr/bin/env python
# -*- coding: utf-8 -*-

import os
import sqlite3
from PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlError
from PyQt5.QtCore import QObject, pyqtSignal


class DatabaseManager(QObject):
    """数据库连接管理类，负责处理与SQLite数据库的连接和基本操作"""
    
    # 信号定义
    connection_established = pyqtSignal(bool, str)  # 连接建立信号(成功状态, 消息)
    query_completed = pyqtSignal(bool, str)  # 查询完成信号(成功状态, 消息)
    
    def __init__(self, db_path=None):
        """初始化数据库管理器
        
        Args:
            db_path: 数据库文件路径，如果为None则使用默认路径
        """
        super(DatabaseManager, self).__init__()
        
        # 设置默认数据库路径
        if db_path is None:
            # 在应用程序数据目录创建数据库
            app_data_dir = os.path.join(os.path.dirname(os.path.dirname(__file__)), 'data')
            if not os.path.exists(app_data_dir):
                os.makedirs(app_data_dir)
            self.db_path = os.path.join(app_data_dir, 'deep_wells.db')
        else:
            self.db_path = db_path
            
        self.db = None
        self.qt_db = None
        self.connected = False
    
    def connect(self):
        """建立数据库连接"""
        try:
            # 创建SQLite连接
            self.db = sqlite3.connect(self.db_path)
            
            # 创建Qt SQL连接
            self.qt_db = QSqlDatabase.addDatabase("QSQLITE")
            self.qt_db.setDatabaseName(self.db_path)
            
            if not self.qt_db.open():
                error = self.qt_db.lastError().text()
                self.connection_established.emit(False, f"无法打开数据库: {error}")
                return False
            
            self.connected = True
            self.connection_established.emit(True, "数据库连接成功")
            return True
            
        except Exception as e:
            self.connection_established.emit(False, f"连接数据库时出错: {str(e)}")
            return False
    
    def disconnect(self):
        """关闭数据库连接"""
        if self.qt_db and self.qt_db.isOpen():
            self.qt_db.close()
        
        if self.db:
            self.db.close()
            
        self.connected = False
        # 使用连接名称移除数据库连接，避免警告
        if self.qt_db:
            connection_name = self.qt_db.connectionName()
            QSqlDatabase.removeDatabase(connection_name)
            self.qt_db = None
    
    def _execute_query(self, query_str, params=None):
        """执行SQL查询
        
        Args:
            query_str: SQL查询字符串
            params: 查询参数(可选)
            
        Returns:
            成功返回True，失败返回False
        """
        if not self.connected:
            self.query_completed.emit(False, "数据库未连接")
            return False
            
        query = QSqlQuery()
        
        if params:
            query.prepare(query_str)
            for key, value in params.items():
                query.bindValue(f":{key}", value)
            success = query.exec_()
        else:
            success = query.exec_(query_str)
            
        if not success:
            error = query.lastError().text()
            self.query_completed.emit(False, f"查询执行失败: {error}")
            return False
            
        self.query_completed.emit(True, "查询执行成功")
        return True
    
    def execute_raw_query(self, query_str, params=None):
        """执行原始SQL查询
        
        Args:
            query_str: SQL查询字符串
            params: 查询参数(可选)
            
        Returns:
            QSqlQuery对象
        """
        query = QSqlQuery()
        
        if params:
            query.prepare(query_str)
            for key, value in params.items():
                query.bindValue(f":{key}", value)
            query.exec_()
        else:
            query.exec_(query_str)
            
        return query
    
    def insert_record(self, table, record_data):
        """向表中插入记录
        
        Args:
            table: 表名
            record_data: 记录数据字典 {字段名: 值}
            
        Returns:
            成功返回新记录ID，失败返回-1
        """
        if not record_data:
            self.query_completed.emit(False, "没有提供数据")
            return -1
            
        fields = ', '.join(record_data.keys())
        placeholders = ', '.join([f":{key}" for key in record_data.keys()])
        
        query_str = f"INSERT INTO {table} ({fields}) VALUES ({placeholders})"
        
        query = QSqlQuery()
        query.prepare(query_str)
        
        for key, value in record_data.items():
            query.bindValue(f":{key}", value)
            
        if not query.exec_():
            error = query.lastError().text()
            self.query_completed.emit(False, f"插入记录失败: {error}")
            return -1
            
        record_id = query.lastInsertId()
        self.query_completed.emit(True, f"成功插入记录，ID: {record_id}")
        return record_id
    
    def update_record(self, table, record_id, record_data):
        """更新表中的记录
        
        Args:
            table: 表名
            record_id: 记录ID
            record_data: 记录数据字典 {字段名: 值}
            
        Returns:
            成功返回True，失败返回False
        """
        if not record_data:
            self.query_completed.emit(False, "没有提供数据")
            return False
            
        set_clause = ', '.join([f"{key} = :{key}" for key in record_data.keys()])
        query_str = f"UPDATE {table} SET {set_clause} WHERE id = :id"
        
        params = record_data.copy()
        params['id'] = record_id
        
        return self._execute_query(query_str, params)
    
    def delete_record(self, table, record_id):
        """删除表中的记录
        
        Args:
            table: 表名
            record_id: 记录ID
            
        Returns:
            成功返回True，失败返回False
        """
        query_str = f"DELETE FROM {table} WHERE id = :id"
        return self._execute_query(query_str, {'id': record_id})
    
    def get_record_by_id(self, table, record_id):
        """根据ID获取记录
        
        Args:
            table: 表名
            record_id: 记录ID
            
        Returns:
            QSqlQuery对象
        """
        query_str = f"SELECT * FROM {table} WHERE id = :id"
        return self.execute_raw_query(query_str, {'id': record_id})
    
    def get_all_records(self, table):
        """获取表中所有记录
        
        Args:
            table: 表名
            
        Returns:
            QSqlQuery对象
        """
        query_str = f"SELECT * FROM {table}"
        return self.execute_raw_query(query_str)
    
    def search_records(self, table, conditions):
        """搜索记录
        
        Args:
            table: 表名
            conditions: 条件字典 {字段名: 值} 或 {字段名: (操作符, 值)}
            
        Returns:
            QSqlQuery对象
        """
        where_clauses = []
        params = {}
        
        for key, value in conditions.items():
            if isinstance(value, tuple) and len(value) == 2:
                operator, val = value
                where_clauses.append(f"{key} {operator} :{key}")
                params[key] = val
            else:
                where_clauses.append(f"{key} = :{key}")
                params[key] = value
                
        where_clause = ' AND '.join(where_clauses)
        query_str = f"SELECT * FROM {table} WHERE {where_clause}"
        
        return self.execute_raw_query(query_str, params)